﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.OracleClient;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;


namespace Sinacor.Infra.Tools
{
    public class DataAccess
    {
        private DbConnection CreateConnection()
        {
            OracleDatabase db = new OracleDatabase("Data Source=SINADES;Persist Security Info=True;User ID=SINATOOLS;Password=SINATOOLS");
            return db.CreateConnection();
        }

		/// <summary>
		/// alterado ORDER BY de "NOME_MOD" para "NOME_MOD,NOME_SUB_MOD,NOME_COTR" - esertorio - 200810101329
		/// </summary>
		/// <returns></returns>
        public List<EndPoint> LoadEndPoints()
		{
            List<EndPoint> endpoints = new List<EndPoint>();
			DbConnection conn = CreateConnection();

			try
			{
				DbCommand cmd = conn.CreateCommand();
				cmd.CommandText = @"
									SELECT 
										COD_ENDP
										,NOME_MOD
										,NOME_SUB_MOD
										,NOME_COTR
										,NOME_PROT
									FROM 
										TFWDENDP_TEMP 
									ORDER BY 
										 NOME_MOD
										,NOME_SUB_MOD
										,NOME_COTR
				";
				conn.Open();
				using (IDataReader dr = cmd.ExecuteReader())
				{
					while (dr.Read())
					{
                        endpoints.Add(new EndPoint(Convert.ToInt32(dr["COD_ENDP"]), dr["NOME_MOD"].ToString(), dr["NOME_SUB_MOD"].ToString(), dr["NOME_COTR"].ToString(), dr["NOME_PROT"].ToString()));
					}
				}
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				if (conn.State != ConnectionState.Closed)
					conn.Close();
			}

			return endpoints;
		}

        public int SaveEndPoint(EndPoint endpoint)
		{
			int linhasAfetadas = 0;
			DbConnection conn = CreateConnection();

			try
			{
				DbCommand cmd = conn.CreateCommand();
				cmd.CommandText = @"
								INSERT INTO 
									TFWDENDP_TEMP (NOME_MOD,NOME_SUB_MOD,NOME_COTR,NOME_PROT)
								VALUES 
									('" + endpoint.Modulo + "','" + endpoint.SubModulo + "','" + endpoint.Contrato + "','" + endpoint.Protocolo + "')";

				conn.Open();
				linhasAfetadas = cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				if (conn.State != ConnectionState.Closed)
					conn.Close();
			}

			return linhasAfetadas;
		}

        public int UpdateEndPoint(EndPoint endpoint)
		{
			int linhasAfetadas = 0;
			DbConnection conn = CreateConnection();

			try
			{
				DbCommand cmd = conn.CreateCommand();
				cmd.CommandText = @"
								UPDATE 
									TFWDENDP_TEMP
								SET 
									NOME_MOD = '" + endpoint.Modulo + @"'
									,NOME_SUB_MOD = '" + endpoint.SubModulo + @"'
									,NOME_COTR = '" + endpoint.Contrato + @"'
									,NOME_PROT = '" + endpoint.Protocolo + @"'
								WHERE 
									COD_ENDP = " + endpoint.CodigoEndPoint.ToString();
				conn.Open();
				linhasAfetadas = cmd.ExecuteNonQuery();
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				if (conn.State != ConnectionState.Closed)
					conn.Close();
			}

			return linhasAfetadas;
		}

        public int DeleteEndPoint(List<EndPoint> endpoints)
		{
			int linhasAfetadas = 0;
			DbConnection conn = CreateConnection();
			
			try
			{
				DbCommand cmd = conn.CreateCommand();
				conn.Open();

                foreach (EndPoint endpoint in endpoints)
				{
					cmd.CommandText = @"
								DELETE FROM 
									TFWDENDP_TEMP 
								WHERE 
									COD_ENDP = " + endpoint.CodigoEndPoint.ToString();

					linhasAfetadas += cmd.ExecuteNonQuery();
				}
			}
			catch (Exception ex)
			{
				throw ex;
			}
			finally
			{
				if (conn.State != ConnectionState.Closed)
					conn.Close();
			}

			return linhasAfetadas;
		}
    }
}